﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using KCL.Code.Entities;
using System.Data;

namespace KCL.Code.Data
{
    public class PayMethodDAO : BaseDAO
    {
        public List<PayMethod> GetPayMethod(int maximumRows, int startRowIndex, string sortExpression)
        {
            using (SqlConnection connection = GetConnection())
            {
                string sqlStatement = string.Format("WITH OrderedPayMethod AS (SELECT *, ROW_NUMBER() OVER (ORDER BY {0}) AS 'RowNumber' FROM [PayMethod]) SELECT * FROM OrderedPayMethod WHERE RowNumber BETWEEN {1} AND {2}", sortExpression != string.Empty ? sortExpression : "id", startRowIndex, startRowIndex + maximumRows);
                SqlCommand command = new SqlCommand(sqlStatement, connection);
                SqlDataAdapter sqlAdapter = new SqlDataAdapter(command);
                connection.Open();
                DataTable dt = new DataTable();

                try
                {
                    sqlAdapter.Fill(dt);
                    List<PayMethod> results = new List<PayMethod>();
                    int currentIndex = 0;
                    while (currentIndex < dt.Rows.Count)
                    {
                        PayMethod PayMethod = new PayMethod();
                        PayMethod.id = Convert.ToInt32(dt.Rows[currentIndex]["id"]);
                        PayMethod.name = dt.Rows[currentIndex]["name"].ToString();
                        results.Add(PayMethod);
                        currentIndex++;
                    }
                    return results;
                }
                catch 
                {
                    return null;
                }
            }
        }

        public int GetTotalPayMethod()
        {
            using (SqlConnection connection = GetConnection())
            {                
                string sqlStatement = "SELECT COUNT(*) FROM [PayMethod]";
                SqlCommand command = new SqlCommand(sqlStatement, connection);
                connection.Open();
                int totalUser = Convert.ToInt32(command.ExecuteScalar());                
                return totalUser;
            }
        }

        public bool CreatePayMethod(PayMethod PayMethod)
        { 
            using (SqlConnection connection = GetConnection())
            {
                string sqlStatement = string.Format("INSERT INTO [PayMethod]([name]) VALUES(N'{0}')", PayMethod.name);
                SqlCommand command = new SqlCommand(sqlStatement, connection);
                try
                {
                    connection.Open();
                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch
                {
                    return false;
                }                   
            }
        }

        public bool UpdatePayMethod(PayMethod PayMethod)
        {
            SqlConnection connection = this.GetConnection();
            string sqlStatement = string.Format("UPDATE [PayMethod] SET name=N'{0}' WHERE id={2}", PayMethod.name, PayMethod.id);
            SqlCommand command = new SqlCommand(sqlStatement, connection);
            try
            {
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }
            catch
            {
                return false;
            }
            return true;
        }

		public List<PayMethod> GetAllPayMethods()
		{
			using (SqlConnection connection = GetConnection())
			{
				string sqlStatement = "SELECT * FROM PayMethod";
				SqlCommand command = new SqlCommand(sqlStatement, connection);
				SqlDataAdapter sqlAdapter = new SqlDataAdapter(command);
				connection.Open();
				DataTable dt = new DataTable();

				try
				{
					sqlAdapter.Fill(dt);
					List<PayMethod> results = new List<PayMethod>();
					int currentIndex = 0;
					while (currentIndex < dt.Rows.Count)
					{
						PayMethod PayMethod = new PayMethod();
						PayMethod.id = Convert.ToInt32(dt.Rows[currentIndex]["id"]);
						PayMethod.name = dt.Rows[currentIndex]["name"].ToString();
						results.Add(PayMethod);
						currentIndex++;
					}
					return results;
				}
				catch
				{
					return null;
				}
			}
		}
    }
}